package com.clj.service.impl;

import com.clj.entity.StudentDO_clj;
import com.clj.req.StudentRequest_clj;
import com.clj.res.TableDTO_clj;
import com.clj.service.StudentService_clj;
import com.clj.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * Author: Harry Chen
 * Date: 2023/02/24 15:56
 * Description:
 */
public class StudentServiceCljImpl_clj implements StudentService_clj {
    public TableDTO_clj retrieveStudents(StudentRequest_clj request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from 学生成绩表_陈林杰");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where 姓名 like '%" + request.getSearchKey().trim() + "%'");
        }
        sql.append(" order by id  asc limit ").append(request.getStart()).append(",")
                .append(request.getPageSize());


        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO_clj returnDTO = new TableDTO_clj();

        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from 学生成绩表_陈林杰");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where 姓名 like '%" + request.getSearchKey().trim() + "%'");
            }

            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    public boolean add(StudentDO_clj studentDOClj) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into 学生成绩表_陈林杰(姓名,学号,住址,语文,数学,英语)");
        sql.append(" values(?,?,?,?,?,?) ");
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1, studentDOClj.get姓名());
            ps.setString(2, studentDOClj.get学号());
            ps.setString(3, studentDOClj.get住址());
            ps.setDouble(4, studentDOClj.get语文());
            ps.setDouble(5, studentDOClj.get数学());
            ps.setDouble(6, studentDOClj.get英语());
            return ps.executeLargeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    //8：28新增
    public StudentDO_clj getById(int selectedStudentId) {
        StringBuilder sql = new StringBuilder("select * from 学生成绩表_陈林杰 where id =  ? ");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StudentDO_clj studentDOClj = new StudentDO_clj();

        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, selectedStudentId);
            rs = ps.executeQuery();
            while (rs.next()) {
                //处理查出的每一条记录
                int id = rs.getInt("id");
                String name = rs.getString("姓名");
                String number = rs.getString("学号");
                String address = rs.getString("住址");
                double chinese = rs.getDouble("语文");
                double english = rs.getDouble("数学");
                double math = rs.getDouble("英语");
                double score = chinese + english + math;
                studentDOClj.setId(id);
                studentDOClj.set姓名(name);
                studentDOClj.set学号(number);
                studentDOClj.set姓名(address);
                studentDOClj.set语文(chinese);
                studentDOClj.set英语(english);
                studentDOClj.set数学(math);

            }
            return studentDOClj;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    public boolean update(StudentDO_clj studentDOClj) {
        StringBuilder sql = new StringBuilder();
        sql.append("update 学生成绩表_陈林杰 set 姓名 = ? ,学号 = ?,住址 = ?,语文 = ?,数学 = ?,英语 = ?");
        sql.append(" where id  =  ?  ");
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1, studentDOClj.get姓名());
            ps.setString(2, studentDOClj.get学号());
            ps.setString(3, studentDOClj.get住址());
            ps.setDouble(4, studentDOClj.get语文());
            ps.setDouble(5, studentDOClj.get数学());
            ps.setDouble(6, studentDOClj.get英语());
            ps.setInt(7, studentDOClj.getId());
            return ps.executeLargeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    public boolean delete(int[] selectedStudentIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete from 学生成绩表_陈林杰 where id in ( ");
        int length = selectedStudentIds.length;
        for (int i = 0; i < length; i++) {
            if (i == (length - 1)) {
                sql.append(" ? ");
            } else {
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {
                //设置参数，从1开始
                ps.setInt(i + 1, selectedStudentIds[i]);
            }
            return ps.executeUpdate() == length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    private Vector<Vector<Object>> fillData(ResultSet rs) {
        Vector<Vector<Object>> data = new Vector<>();
        try {
            while (rs.next()) {
                //处理查出的每一条记录
                Vector<Object> oneRecord = new Vector<>();
                int id = rs.getInt("id");
                String name = rs.getString("姓名");
                String no = rs.getString("学号");
                String hometown = rs.getString("住址");
                int cnScore = rs.getInt("语文");
                int mathScore = rs.getInt("数学");
                int enScore = rs.getInt("英语");
                int totalScore = cnScore + mathScore + enScore;
                oneRecord.addElement(id);
                oneRecord.addElement(name);
                oneRecord.addElement(no);
                oneRecord.addElement(hometown);
                oneRecord.addElement(cnScore);
                oneRecord.addElement(mathScore);
                oneRecord.addElement(enScore);
                oneRecord.addElement(totalScore);
                data.addElement(oneRecord);

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return data;
    }
}
